#include "databasemanager.h"
#include <QSqlQuery>
#include <QSqlError>
#include <QVariant>
#include <QDebug>
#include <QCryptographicHash>
#include <QThread>

QAtomicInt DatabaseManager::m_connectionCounter(0);

DatabaseManager& DatabaseManager::instance() {
    static DatabaseManager instance;
    return instance;
}

DatabaseManager::DatabaseManager() : m_port(3306), m_isConfigured(false) {
    m_database = QSqlDatabase::addDatabase("QMYSQL", "main_thread_connection");
}

DatabaseManager::~DatabaseManager() {
    closeDatabase();
}

bool DatabaseManager::connectToDatabase(const QString& host, int port, const QString& dbName, 
                                       const QString& user, const QString& password) {
    // 保存连接参数，用于创建线程本地连接
    QMutexLocker locker(&m_mutex);
    m_host = host;
    m_port = port;
    m_dbName = dbName;
    m_user = user;
    m_password = password;
    m_isConfigured = true;
    locker.unlock();
    
    m_database.setHostName(host);
    m_database.setPort(port);
    m_database.setDatabaseName(dbName);
    m_database.setUserName(user);
    m_database.setPassword(password);
    
    // ===== 云数据库性能优化配置 =====
    
    // 启用连接选项
    QString connectOptions;
    
    // 1. 启用压缩传输（减少网络延迟）
    connectOptions += "CLIENT_COMPRESS=1;";
    
    // 2. 设置连接超时（避免长时间等待）
    connectOptions += "MYSQL_OPT_CONNECT_TIMEOUT=10;";
    
    // 3. 设置读写超时
    connectOptions += "MYSQL_OPT_READ_TIMEOUT=30;";
    connectOptions += "MYSQL_OPT_WRITE_TIMEOUT=30;";
    
    // 4. 启用自动重连
    connectOptions += "MYSQL_OPT_RECONNECT=1;";
    
    m_database.setConnectOptions(connectOptions);
    
    qDebug() << "数据库连接配置:" << connectOptions;
    
    if (!m_database.open()) {
        m_lastError = m_database.lastError().text();
        qDebug() << "数据库连接失败:" << m_lastError;
        return false;
    }
    
    qDebug() << "数据库连接成功";
    
    // 执行连接后的优化设置
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    QSqlQuery query(db);
    
    // 设置会话级别的优化参数
    query.exec("SET SESSION sql_mode = 'TRADITIONAL'");
    query.exec("SET SESSION innodb_lock_wait_timeout = 10");
    
    // 设置字符集为 utf8mb4
    query.exec("SET NAMES utf8mb4");
    
    // 启用查询结果集缓存（客户端侧）
    query.exec("SET SESSION query_cache_type = ON");
    
    qDebug() << "数据库会话优化配置完成";
    
    return true;
}

void DatabaseManager::closeDatabase() {
    if (m_database.isOpen()) {
        m_database.close();
    }
}

bool DatabaseManager::isConnected() const {
    return m_database.isOpen();
}

// 为当前线程获取独立的数据库连接
QSqlDatabase DatabaseManager::getThreadDatabase() {
    // 使用线程ID作为连接名，确保每个线程复用同一个连接
    QString connectionName = QString("thread_%1")
        .arg(reinterpret_cast<quintptr>(QThread::currentThreadId()));
    
    QSqlDatabase db;
    
    // 检查该连接是否已存在
    if (QSqlDatabase::contains(connectionName)) {
        db = QSqlDatabase::database(connectionName);
        
        // 检查连接是否有效
        if (db.isOpen() && db.isValid()) {
            // 尝试执行一个简单的查询来验证连接
            QSqlQuery testQuery("SELECT 1", db);
            if (testQuery.exec()) {
                return db;
            }
            
            // 如果 ping 失败，尝试重新打开连接
            qDebug() << "数据库连接失效，尝试重新连接:" << connectionName;
            if (db.isOpen()) {
                db.close();
            }
            if (db.open()) {
                QSqlQuery query(db);
                query.exec("SET SESSION sql_mode = 'TRADITIONAL'");
                query.exec("SET SESSION innodb_lock_wait_timeout = 10");
                query.exec("SET NAMES utf8mb4");
                query.exec("SET SESSION query_cache_type = ON");
                qDebug() << "数据库连接重连成功:" << connectionName;
                return db;
            }
        }
        
        // 如果连接无法恢复，删除并重新创建
        if (db.isOpen()) {
            db.close();
        }
        QSqlDatabase::removeDatabase(connectionName);
    }
    
    // 创建新连接
    QMutexLocker locker(&m_mutex);
    if (!m_isConfigured) {
        qDebug() << "数据库尚未配置";
        return QSqlDatabase();
    }
    
    db = QSqlDatabase::addDatabase("QMYSQL", connectionName);
    db.setHostName(m_host);
    db.setPort(m_port);
    db.setDatabaseName(m_dbName);
    db.setUserName(m_user);
    db.setPassword(m_password);
    
    // 设置连接选项
    QString connectOptions;
    connectOptions += "CLIENT_COMPRESS=1;";
    connectOptions += "MYSQL_OPT_CONNECT_TIMEOUT=10;";
    connectOptions += "MYSQL_OPT_READ_TIMEOUT=30;";
    connectOptions += "MYSQL_OPT_WRITE_TIMEOUT=30;";
    connectOptions += "MYSQL_OPT_RECONNECT=1;";
    db.setConnectOptions(connectOptions);
    
    locker.unlock();
    
    if (!db.open()) {
        qDebug() << "线程数据库连接失败:" << connectionName << db.lastError().text();
        return QSqlDatabase();
    }
    
    // 设置会话优化参数
    QSqlQuery query(db);
    query.exec("SET SESSION sql_mode = 'TRADITIONAL'");
    query.exec("SET SESSION innodb_lock_wait_timeout = 10");
    query.exec("SET NAMES utf8mb4");
    query.exec("SET SESSION query_cache_type = ON");
    
    qDebug() << "线程数据库连接创建成功:" << connectionName;
    
    return db;
}

bool DatabaseManager::authenticateUser(const QString& username, const QString& password, User& user) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) {
        m_lastError = "无法获取数据库连接";
        return false;
    }
    
    QSqlQuery query(db);
    
    // 客户端已经对密码进行了哈希，直接使用传入的密码（已经是哈希值）
    
    query.prepare("SELECT * FROM users WHERE username = :username AND password = :password");
    query.bindValue(":username", username);
    query.bindValue(":password", password); // 直接使用已哈希的密码
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        return false;
    }
    
    if (query.next()) {
        user.id = query.value("id").toInt();
        user.username = query.value("username").toString();
        user.realName = query.value("real_name").toString();
        user.idCard = query.value("id_card").toString();
        user.phone = query.value("phone").toString();
        user.role = query.value("role").toInt();
        user.createTime = query.value("create_time").toDateTime();
        return true;
    }
    
    m_lastError = "用户名或密码错误";
    return false;
}

bool DatabaseManager::registerUser(const User& user) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) {
        m_lastError = "无法获取数据库连接";
        return false;
    }
    
    QSqlQuery query(db);
    
    // 客户端已经对密码进行了哈希，服务器端不需要再次哈希
    // 直接使用传入的密码（已经是哈希值）
    
    query.prepare("INSERT INTO users (username, password, real_name, id_card, phone, role) "
                 "VALUES (:username, :password, :realName, :idCard, :phone, :role)");
    query.bindValue(":username", user.username);
    query.bindValue(":password", user.password); // 使用已哈希的密码
    query.bindValue(":realName", user.realName);
    query.bindValue(":idCard", user.idCard);
    query.bindValue(":phone", user.phone);
    query.bindValue(":role", user.role);
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        qDebug() << "注册失败:" << m_lastError;
        return false;
    }
    
    qDebug() << "用户注册成功，ID:" << query.lastInsertId();
    return true;
}

bool DatabaseManager::getPatientInfo(int patientId, Patient& patient) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    QSqlQuery query(db);
    
    // 先获取基本用户信息
    query.prepare("SELECT * FROM users WHERE id = :userId");
    query.bindValue(":userId", patientId);
    
    if (!query.exec() || !query.next()) {
        m_lastError = "未找到用户信息";
        return false;
    }
    
    patient.id = query.value("id").toInt();
    patient.username = query.value("username").toString();
    patient.realName = query.value("real_name").toString();
    patient.idCard = query.value("id_card").toString();
    patient.phone = query.value("phone").toString();
    patient.role = query.value("role").toInt();
    
    // 尝试获取患者详细信息（可能不存在）
    query.prepare("SELECT * FROM patients WHERE user_id = :userId");
    query.bindValue(":userId", patientId);
    
    if (query.exec() && query.next()) {
        patient.gender = query.value("gender").toString();
        patient.birthDate = query.value("birth_date").toDate();
        patient.address = query.value("address").toString();
        patient.emergencyContact = query.value("emergency_contact").toString();
        patient.emergencyPhone = query.value("emergency_phone").toString();
        patient.allergies = query.value("allergies").toString();
        patient.medicalHistory = query.value("medical_history").toString();
    } else {
        // 患者详细信息不存在，使用默认值
        patient.gender = "男";
        patient.birthDate = QDate();
        patient.address = "";
        patient.emergencyContact = "";
        patient.emergencyPhone = "";
        patient.allergies = "";
        patient.medicalHistory = "";
    }
    
    return true;
}

bool DatabaseManager::updatePatientInfo(const Patient& patient) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    QSqlQuery query(db);
    
    // 首先检查 patients 表中是否已有记录
    query.prepare("SELECT id FROM patients WHERE user_id = :userId");
    query.bindValue(":userId", patient.id);
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        return false;
    }
    
    if (query.next()) {
        // 已存在，更新记录
        query.prepare("UPDATE patients SET gender=:gender, birth_date=:birthDate, "
                     "address=:address, emergency_contact=:emergencyContact, "
                     "emergency_phone=:emergencyPhone, allergies=:allergies, "
                     "medical_history=:medicalHistory WHERE user_id=:userId");
    } else {
        // 不存在，插入新记录
        query.prepare("INSERT INTO patients (user_id, gender, birth_date, address, "
                     "emergency_contact, emergency_phone, allergies, medical_history) VALUES "
                     "(:userId, :gender, :birthDate, :address, :emergencyContact, "
                     ":emergencyPhone, :allergies, :medicalHistory)");
    }
    
    query.bindValue(":userId", patient.id);
    query.bindValue(":gender", patient.gender);
    query.bindValue(":birthDate", patient.birthDate);
    query.bindValue(":address", patient.address);
    query.bindValue(":emergencyContact", patient.emergencyContact);
    query.bindValue(":emergencyPhone", patient.emergencyPhone);
    query.bindValue(":allergies", patient.allergies);
    query.bindValue(":medicalHistory", patient.medicalHistory);
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        qDebug() << "更新患者信息失败:" << m_lastError;
        return false;
    }
    
    qDebug() << "患者信息更新成功";
    return true;
}

bool DatabaseManager::createAppointment(const Appointment& appointment, int& appointmentId) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    QSqlQuery query(db);
    
    query.prepare("INSERT INTO appointments (patient_id, doctor_id, appointment_time, "
                 "status, symptoms) VALUES (:patientId, :doctorId, :appointmentTime, "
                 ":status, :symptoms)");
    query.bindValue(":patientId", appointment.patientId);
    query.bindValue(":doctorId", appointment.doctorId);
    query.bindValue(":appointmentTime", appointment.appointmentTime);
    query.bindValue(":status", appointment.status);
    query.bindValue(":symptoms", appointment.symptoms);
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        return false;
    }
    
    appointmentId = query.lastInsertId().toInt();
    
    // 获取患者姓名
    QSqlQuery patientQuery(db);
    patientQuery.prepare("SELECT real_name FROM users WHERE id = :patientId");
    patientQuery.bindValue(":patientId", appointment.patientId);
    QString patientName = "患者";
    if (patientQuery.exec() && patientQuery.next()) {
        patientName = patientQuery.value("real_name").toString();
    }
    
    // 创建通知给医生
    Notification notification;
    notification.userId = appointment.doctorId;
    notification.userRole = 2;  // 医生
    notification.type = "new_appointment";
    notification.title = "📅 新预约通知";
    notification.content = QString("患者：%1\n预约时间：%2\n症状：%3")
        .arg(patientName)
        .arg(appointment.appointmentTime.toString("yyyy-MM-dd HH:mm"))
        .arg(appointment.symptoms);
    notification.relatedId = appointmentId;
    notification.isRead = false;
    createNotification(notification);
    
    return true;
}

bool DatabaseManager::cancelAppointment(int appointmentId) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    
    // 先获取预约信息
    QSqlQuery selectQuery(db);
    selectQuery.prepare("SELECT doctor_id, patient_id, appointment_time FROM appointments WHERE id = :appointmentId");
    selectQuery.bindValue(":appointmentId", appointmentId);
    
    int doctorId = 0;
    int patientId = 0;
    QString appointmentTime;
    
    if (selectQuery.exec() && selectQuery.next()) {
        doctorId = selectQuery.value("doctor_id").toInt();
        patientId = selectQuery.value("patient_id").toInt();
        appointmentTime = selectQuery.value("appointment_time").toString();
    }
    
    // 更新预约状态为已取消
    QSqlQuery updateQuery(db);
    updateQuery.prepare("UPDATE appointments SET status = '已取消', cancel_reason = '患者主动取消' WHERE id = :appointmentId");
    updateQuery.bindValue(":appointmentId", appointmentId);
    
    if (!updateQuery.exec()) {
        m_lastError = updateQuery.lastError().text();
        qDebug() << "取消预约失败:" << m_lastError;
        return false;
    }
    
    if (updateQuery.numRowsAffected() == 0) {
        return false;
    }
    
    // 获取患者姓名
    QSqlQuery patientQuery(db);
    patientQuery.prepare("SELECT real_name FROM users WHERE id = :patientId");
    patientQuery.bindValue(":patientId", patientId);
    QString patientName = "患者";
    if (patientQuery.exec() && patientQuery.next()) {
        patientName = patientQuery.value("real_name").toString();
    }
    
    // 创建通知给医生
    Notification notification;
    notification.userId = doctorId;
    notification.userRole = 2;  // 医生
    notification.type = "appointment_cancelled_by_patient";
    notification.title = "📅 预约已取消";
    notification.content = QString("患者：%1\n预约时间：%2\n原因：患者主动取消")
        .arg(patientName, appointmentTime);
    notification.relatedId = appointmentId;
    notification.isRead = false;
    createNotification(notification);
    
    return true;
}

// 批量取消时间段内的预约
bool DatabaseManager::cancelAppointmentsInRange(int doctorId, const QDate& startDate, const QDate& endDate) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    
    QString cancelReason = QString("医生请假（%1 至 %2）")
        .arg(startDate.toString("yyyy-MM-dd"))
        .arg(endDate.toString("yyyy-MM-dd"));
    
    // 先查询要取消的预约，获取患者ID
    QSqlQuery selectQuery(db);
    selectQuery.prepare("SELECT id, patient_id, appointment_time FROM appointments "
                       "WHERE doctor_id = :doctorId "
                       "AND DATE(appointment_time) BETWEEN :startDate AND :endDate "
                       "AND status != '已取消'");
    selectQuery.bindValue(":doctorId", doctorId);
    selectQuery.bindValue(":startDate", startDate.toString("yyyy-MM-dd"));
    selectQuery.bindValue(":endDate", endDate.toString("yyyy-MM-dd"));
    
    QList<int> patientIds;
    QList<int> appointmentIds;
    QList<QString> appointmentTimes;
    
    if (selectQuery.exec()) {
        while (selectQuery.next()) {
            appointmentIds.append(selectQuery.value("id").toInt());
            patientIds.append(selectQuery.value("patient_id").toInt());
            appointmentTimes.append(selectQuery.value("appointment_time").toString());
        }
    }
    
    // 更新预约状态
    QSqlQuery updateQuery(db);
    updateQuery.prepare("UPDATE appointments SET status = '已取消', cancel_reason = :reason "
                       "WHERE doctor_id = :doctorId "
                       "AND DATE(appointment_time) BETWEEN :startDate AND :endDate "
                       "AND status != '已取消'");
    updateQuery.bindValue(":reason", cancelReason);
    updateQuery.bindValue(":doctorId", doctorId);
    updateQuery.bindValue(":startDate", startDate.toString("yyyy-MM-dd"));
    updateQuery.bindValue(":endDate", endDate.toString("yyyy-MM-dd"));
    
    if (!updateQuery.exec()) {
        m_lastError = updateQuery.lastError().text();
        qDebug() << "批量取消预约失败:" << m_lastError;
        return false;
    }
    
    // 为每个患者创建通知
    for (int i = 0; i < patientIds.size(); ++i) {
        Notification notification;
        notification.userId = patientIds[i];
        notification.userRole = 1;  // 患者
        notification.type = "appointment_cancelled";
        notification.title = "📅 您的预约已取消";
        notification.content = QString("原因：%1\n预约时间：%2").arg(cancelReason, appointmentTimes[i]);
        notification.relatedId = appointmentIds[i];
        notification.isRead = false;
        createNotification(notification);
    }
    
    return true;
}

bool DatabaseManager::updateAppointmentStatus(int appointmentId, const QString& status) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    QSqlQuery query(db);
    
    query.prepare("UPDATE appointments SET status = :status WHERE id = :appointmentId");
    query.bindValue(":status", status);
    query.bindValue(":appointmentId", appointmentId);
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        qDebug() << "更新预约状态失败:" << m_lastError;
        return false;
    }
    
    return query.numRowsAffected() > 0;
}

void DatabaseManager::checkAppointmentCompletion(int appointmentId, bool& hasRecord, bool& hasPrescription) {
    hasRecord = false;
    hasPrescription = false;
    
    qDebug() << "=== 检查预约完成情况 ===";
    qDebug() << "预约ID:" << appointmentId;
    
    // 检查是否存在该预约对应的病历（基于 appointment_id）
    QSqlQuery recordQuery(m_database);
    recordQuery.prepare("SELECT COUNT(*) FROM medical_records WHERE appointment_id = :appointmentId");
    recordQuery.bindValue(":appointmentId", appointmentId);
    
    if (recordQuery.exec() && recordQuery.next()) {
        int count = recordQuery.value(0).toInt();
        hasRecord = (count > 0);
        qDebug() << "  病历查询结果: COUNT =" << count;
    } else {
        qDebug() << "  病历查询失败:" << recordQuery.lastError().text();
    }
    
    // 检查是否存在该预约对应的处方（基于 appointment_id）
    QSqlQuery prescriptionQuery(m_database);
    prescriptionQuery.prepare("SELECT COUNT(*) FROM prescriptions WHERE appointment_id = :appointmentId");
    prescriptionQuery.bindValue(":appointmentId", appointmentId);
    
    if (prescriptionQuery.exec() && prescriptionQuery.next()) {
        int count = prescriptionQuery.value(0).toInt();
        hasPrescription = (count > 0);
        qDebug() << "  处方查询结果: COUNT =" << count;
    } else {
        qDebug() << "  处方查询失败:" << prescriptionQuery.lastError().text();
    }
    
    qDebug() << "检查结果 - 病历:" << hasRecord << "处方:" << hasPrescription;
}

QList<Appointment> DatabaseManager::getPatientAppointments(int patientId) {
    QList<Appointment> appointments;
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return QList<Appointment>();
    QSqlQuery query(db);
    
    query.prepare("SELECT * FROM appointments WHERE patient_id = :patientId "
                 "ORDER BY appointment_time DESC");
    query.bindValue(":patientId", patientId);
    
    if (query.exec()) {
        while (query.next()) {
            Appointment appointment;
            appointment.id = query.value("id").toInt();
            appointment.patientId = query.value("patient_id").toInt();
            appointment.doctorId = query.value("doctor_id").toInt();
            appointment.appointmentTime = query.value("appointment_time").toDateTime();
            appointment.status = query.value("status").toString();
            appointment.cancelReason = query.value("cancel_reason").toString();
            appointment.symptoms = query.value("symptoms").toString();
            appointment.medicalRecordId = query.value("medical_record_id").toInt();
            appointment.createTime = query.value("create_time").toDateTime();
            appointments.append(appointment);
        }
    }
    
    return appointments;
}

QList<MedicalRecord> DatabaseManager::getPatientMedicalRecords(int patientId) {
    QList<MedicalRecord> records;
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return QList<MedicalRecord>();
    QSqlQuery query(db);
    
    query.prepare("SELECT mr.*, u.real_name as doctor_name FROM medical_records mr "
                 "LEFT JOIN users u ON mr.doctor_id = u.id "
                 "WHERE mr.patient_id = :patientId "
                 "ORDER BY mr.visit_time DESC");
    query.bindValue(":patientId", patientId);
    
    if (query.exec()) {
        while (query.next()) {
            MedicalRecord record;
            record.id = query.value("id").toInt();
            record.patientId = query.value("patient_id").toInt();
            record.doctorId = query.value("doctor_id").toInt();
            record.appointmentId = query.value("appointment_id").toInt();
            record.doctorName = query.value("doctor_name").toString();
            record.visitTime = query.value("visit_time").toDateTime();
            record.chiefComplaint = query.value("chief_complaint").toString();
            record.presentIllness = query.value("present_illness").toString();
            record.physicalExam = query.value("physical_exam").toString();
            record.diagnosis = query.value("diagnosis").toString();
            record.treatment = query.value("treatment").toString();
            record.notes = query.value("notes").toString();
            records.append(record);
        }
    }
    
    return records;
}

QList<Prescription> DatabaseManager::getPatientPrescriptions(int patientId) {
    QList<Prescription> prescriptions;
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return QList<Prescription>();
    QSqlQuery query(db);
    
    query.prepare("SELECT * FROM prescriptions WHERE patient_id = :patientId "
                 "ORDER BY prescription_time DESC");
    query.bindValue(":patientId", patientId);
    
    qDebug() << "查询患者处方 - 患者ID:" << patientId;
    
    if (query.exec()) {
        while (query.next()) {
            Prescription prescription;
            prescription.id = query.value("id").toInt();
            prescription.medicalRecordId = query.value("medical_record_id").toInt();
            prescription.patientId = query.value("patient_id").toInt();
            prescription.doctorId = query.value("doctor_id").toInt();
            prescription.prescriptionTime = query.value("prescription_time").toDateTime();
            prescription.medications = query.value("medications").toString();
            prescription.dosage = query.value("dosage").toString();
            prescription.usage = query.value("usage").toString();
            prescription.frequency = query.value("frequency").toString();
            prescription.days = query.value("days").toInt();
            prescription.notes = query.value("notes").toString();
            
            qDebug() << "  处方ID:" << prescription.id 
                     << "时间(数据库):" << query.value("prescription_time")
                     << "时间(对象):" << prescription.prescriptionTime
                     << "是否有效:" << prescription.prescriptionTime.isValid();
            
            prescriptions.append(prescription);
        }
        qDebug() << "  共查询到" << prescriptions.count() << "条处方";
    } else {
        qDebug() << "查询处方失败:" << query.lastError().text();
    }
    
    return prescriptions;
}

bool DatabaseManager::getDoctorInfo(int doctorId, Doctor& doctor) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    QSqlQuery query(db);
    
    // 先获取基本用户信息
    query.prepare("SELECT * FROM users WHERE id = :userId");
    query.bindValue(":userId", doctorId);
    
    if (!query.exec() || !query.next()) {
        m_lastError = "未找到用户信息";
        return false;
    }
    
    doctor.id = query.value("id").toInt();
    doctor.username = query.value("username").toString();
    doctor.realName = query.value("real_name").toString();
    doctor.idCard = query.value("id_card").toString();
    doctor.phone = query.value("phone").toString();
    doctor.role = query.value("role").toInt();
    
    // 尝试获取医生详细信息（可能不存在）
    query.prepare("SELECT d.*, dep.name as dept_name FROM doctors d "
                 "LEFT JOIN departments dep ON d.department_id = dep.id "
                 "WHERE d.user_id = :userId");
    query.bindValue(":userId", doctorId);
    
    if (query.exec() && query.next()) {
        doctor.departmentId = query.value("department_id").toInt();
        doctor.departmentName = query.value("dept_name").toString();
        doctor.title = query.value("title").toString();
        doctor.specialty = query.value("specialty").toString();
        doctor.education = query.value("education").toString();
        doctor.experience = query.value("experience").toInt();
        doctor.introduction = query.value("introduction").toString();
        doctor.workTime = query.value("work_time").toString();
        doctor.consultationFee = query.value("consultation_fee").toDouble();
        doctor.dailyPatientLimit = query.value("daily_patient_limit").toInt();
        doctor.clinicType = query.value("clinic_type").toString();
    } else {
        // 医生详细信息不存在，使用默认值
        doctor.departmentId = 0;
        doctor.departmentName = "未设置";
        doctor.title = "";
        doctor.specialty = "";
        doctor.education = "本科";
        doctor.experience = 0;
        doctor.introduction = "";
        doctor.workTime = "";
        doctor.clinicType = "普通门诊";
        doctor.consultationFee = 0.0;
        doctor.dailyPatientLimit = 20;
    }
    
    return true;
}

bool DatabaseManager::updateDoctorInfo(const Doctor& doctor) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    QSqlQuery query(db);
    
    // 首先检查 doctors 表中是否已有记录
    query.prepare("SELECT id FROM doctors WHERE user_id = :userId");
    query.bindValue(":userId", doctor.id);
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        return false;
    }
    
    if (query.next()) {
        // 已存在，更新记录
        query.prepare("UPDATE doctors SET department_id=:deptId, title=:title, "
                     "specialty=:specialty, education=:education, experience=:experience, "
                     "introduction=:intro, work_time=:workTime, consultation_fee=:consultationFee, "
                     "daily_patient_limit=:dailyLimit, clinic_type=:clinicType WHERE user_id=:userId");
    } else {
        // 不存在，插入新记录
        query.prepare("INSERT INTO doctors (user_id, department_id, title, specialty, "
                     "education, experience, introduction, work_time, consultation_fee, daily_patient_limit, clinic_type) VALUES "
                     "(:userId, :deptId, :title, :specialty, :education, :experience, :intro, :workTime, :consultationFee, :dailyLimit, :clinicType)");
    }
    
    query.bindValue(":userId", doctor.id);
    query.bindValue(":deptId", doctor.departmentId);
    query.bindValue(":title", doctor.title);
    query.bindValue(":specialty", doctor.specialty);
    query.bindValue(":education", doctor.education);
    query.bindValue(":experience", doctor.experience);
    query.bindValue(":intro", doctor.introduction);
    query.bindValue(":workTime", doctor.workTime);
    query.bindValue(":consultationFee", doctor.consultationFee);
    query.bindValue(":dailyLimit", doctor.dailyPatientLimit);
    query.bindValue(":clinicType", doctor.clinicType.isEmpty() ? "普通门诊" : doctor.clinicType);
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        qDebug() << "更新医生信息失败:" << m_lastError;
        return false;
    }
    
    qDebug() << "医生信息更新成功";
    return true;
}

QList<Appointment> DatabaseManager::getDoctorAppointments(int doctorId, const QDate& date) {
    QList<Appointment> appointments;
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return QList<Appointment>();
    QSqlQuery query(db);
    
    query.prepare("SELECT * FROM appointments WHERE doctor_id = :doctorId "
                 "AND DATE(appointment_time) = :date ORDER BY appointment_time");
    query.bindValue(":doctorId", doctorId);
    query.bindValue(":date", date);
    
    qDebug() << "查询医生预约 - 医生ID:" << doctorId << "日期:" << date;
    
    if (query.exec()) {
        int count = 0;
        while (query.next()) {
            Appointment appointment;
            appointment.id = query.value("id").toInt();
            appointment.patientId = query.value("patient_id").toInt();
            appointment.doctorId = query.value("doctor_id").toInt();
            appointment.appointmentTime = query.value("appointment_time").toDateTime();
            appointment.status = query.value("status").toString();
            appointment.cancelReason = query.value("cancel_reason").toString();
            appointment.symptoms = query.value("symptoms").toString();
            appointment.medicalRecordId = query.value("medical_record_id").toInt();
            appointment.createTime = query.value("create_time").toDateTime();
            appointments.append(appointment);
            count++;
            qDebug() << "  找到预约" << count << ": 患者ID=" << appointment.patientId 
                     << "时间=" << appointment.appointmentTime;
        }
        qDebug() << "总共找到" << count << "个预约";
    } else {
        qDebug() << "查询失败:" << query.lastError().text();
    }
    
    return appointments;
}

bool DatabaseManager::createMedicalRecord(const MedicalRecord& record, int& recordId) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    QSqlQuery query(db);
    
    query.prepare("INSERT INTO medical_records (patient_id, doctor_id, appointment_id, visit_time, "
                 "chief_complaint, present_illness, physical_exam, diagnosis, treatment, notes) "
                 "VALUES (:patientId, :doctorId, :appointmentId, :visitTime, :chiefComplaint, "
                 ":presentIllness, :physicalExam, :diagnosis, :treatment, :notes)");
    query.bindValue(":patientId", record.patientId);
    query.bindValue(":doctorId", record.doctorId);
    query.bindValue(":appointmentId", record.appointmentId);
    query.bindValue(":visitTime", record.visitTime);
    query.bindValue(":chiefComplaint", record.chiefComplaint);
    query.bindValue(":presentIllness", record.presentIllness);
    query.bindValue(":physicalExam", record.physicalExam);
    query.bindValue(":diagnosis", record.diagnosis);
    query.bindValue(":treatment", record.treatment);
    query.bindValue(":notes", record.notes);
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        return false;
    }
    
    recordId = query.lastInsertId().toInt();
    
    // 获取医生姓名
    QSqlQuery doctorQuery(db);
    doctorQuery.prepare("SELECT real_name FROM users WHERE id = :doctorId");
    doctorQuery.bindValue(":doctorId", record.doctorId);
    QString doctorName = "医生";
    if (doctorQuery.exec() && doctorQuery.next()) {
        doctorName = doctorQuery.value("real_name").toString();
    }
    
    // 创建通知给患者
    Notification notification;
    notification.userId = record.patientId;
    notification.userRole = 1;  // 患者
    notification.type = "medical_record_created";
    notification.title = "📋 病历已创建";
    notification.content = QString("医生：%1\n就诊时间：%2\n诊断：%3")
        .arg(doctorName)
        .arg(record.visitTime.toString("yyyy-MM-dd HH:mm"))
        .arg(record.diagnosis);
    notification.relatedId = recordId;
    notification.isRead = false;
    createNotification(notification);
    
    return true;
}

bool DatabaseManager::createPrescription(const Prescription& prescription) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    QSqlQuery query(db);
    
    qDebug() << "=== 创建处方 - 数据库操作 ===";
    qDebug() << "病历ID:" << prescription.medicalRecordId;
    qDebug() << "患者ID:" << prescription.patientId;
    qDebug() << "医生ID:" << prescription.doctorId;
    qDebug() << "处方时间:" << prescription.prescriptionTime;
    qDebug() << "处方时间是否有效:" << prescription.prescriptionTime.isValid();
    
    query.prepare("INSERT INTO prescriptions (medical_record_id, patient_id, doctor_id, appointment_id, "
                 "prescription_time, medications, dosage, `usage`, frequency, days, notes) "
                 "VALUES (:medicalRecordId, :patientId, :doctorId, :appointmentId, :prescriptionTime, "
                 ":medications, :dosage, :usage, :frequency, :days, :notes)");
    query.bindValue(":medicalRecordId", prescription.medicalRecordId);
    query.bindValue(":patientId", prescription.patientId);
    query.bindValue(":doctorId", prescription.doctorId);
    query.bindValue(":appointmentId", prescription.appointmentId);
    query.bindValue(":prescriptionTime", prescription.prescriptionTime);
    query.bindValue(":medications", prescription.medications);
    query.bindValue(":dosage", prescription.dosage);
    query.bindValue(":usage", prescription.usage);
    query.bindValue(":frequency", prescription.frequency);
    query.bindValue(":days", prescription.days);
    query.bindValue(":notes", prescription.notes);
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        qDebug() << "创建处方失败:" << m_lastError;
        qDebug() << "执行的SQL:" << query.executedQuery();
        return false;
    }
    
    int prescriptionId = query.lastInsertId().toInt();
    
    // 获取医生姓名
    QSqlQuery doctorQuery(db);
    doctorQuery.prepare("SELECT real_name FROM users WHERE id = :doctorId");
    doctorQuery.bindValue(":doctorId", prescription.doctorId);
    QString doctorName = "医生";
    if (doctorQuery.exec() && doctorQuery.next()) {
        doctorName = doctorQuery.value("real_name").toString();
    }
    
    // 创建通知给患者
    Notification notification;
    notification.userId = prescription.patientId;
    notification.userRole = 1;  // 患者
    notification.type = "prescription_created";
    notification.title = "💊 处方已开具";
    notification.content = QString("医生：%1\n药品：%2\n用法：%3")
        .arg(doctorName)
        .arg(prescription.medications)
        .arg(prescription.usage);
    notification.relatedId = prescriptionId;
    notification.isRead = false;
    createNotification(notification);
    
    qDebug() << "创建处方成功";
    return true;
}

bool DatabaseManager::createExamRequest(const ExamRequest& exam) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    QSqlQuery query(db);
    
    query.prepare("INSERT INTO exam_requests (medical_record_id, patient_id, doctor_id, "
                 "exam_type, exam_item, purpose, status, request_time) "
                 "VALUES (:medicalRecordId, :patientId, :doctorId, :examType, "
                 ":examItem, :purpose, :status, :requestTime)");
    query.bindValue(":medicalRecordId", exam.medicalRecordId);
    query.bindValue(":patientId", exam.patientId);
    query.bindValue(":doctorId", exam.doctorId);
    query.bindValue(":examType", exam.examType);
    query.bindValue(":examItem", exam.examItem);
    query.bindValue(":purpose", exam.purpose);
    query.bindValue(":status", exam.status);
    query.bindValue(":requestTime", exam.requestTime);
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        return false;
    }
    
    return true;
}

QList<Department> DatabaseManager::getAllDepartments() {
    QList<Department> departments;
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return QList<Department>();
    QSqlQuery query(db);
    
    if (query.exec("SELECT * FROM departments ORDER BY name")) {
        while (query.next()) {
            Department dept;
            dept.id = query.value("id").toInt();
            dept.name = query.value("name").toString();
            dept.description = query.value("description").toString();
            dept.location = query.value("location").toString();
            dept.phone = query.value("phone").toString();
            departments.append(dept);
        }
    }
    
    return departments;
}

QList<Doctor> DatabaseManager::getDoctorsByDepartment(int departmentId) {
    QList<Doctor> doctors;
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return QList<Doctor>();
    QSqlQuery query(db);
    
    query.prepare("SELECT u.*, d.*, dep.name as dept_name FROM users u "
                 "JOIN doctors d ON u.id = d.user_id "
                 "JOIN departments dep ON d.department_id = dep.id "
                 "WHERE d.department_id = :departmentId");
    query.bindValue(":departmentId", departmentId);
    
    if (query.exec()) {
        while (query.next()) {
            Doctor doctor;
            doctor.id = query.value("id").toInt();
            doctor.username = query.value("username").toString();
            doctor.realName = query.value("real_name").toString();
            doctor.phone = query.value("phone").toString();
            doctor.departmentId = query.value("department_id").toInt();
            doctor.departmentName = query.value("dept_name").toString();
            doctor.title = query.value("title").toString();
            doctor.specialty = query.value("specialty").toString();
            doctor.education = query.value("education").toString();
            doctor.experience = query.value("experience").toInt();
            doctor.introduction = query.value("introduction").toString();
            doctor.workTime = query.value("work_time").toString();
            doctor.consultationFee = query.value("consultation_fee").toDouble();
            doctor.dailyPatientLimit = query.value("daily_patient_limit").toInt();
            doctor.clinicType = query.value("clinic_type").toString();
            
            // 统计今天的已预约人数
            QSqlQuery countQuery(m_database);
            countQuery.prepare("SELECT COUNT(*) as count FROM appointments "
                             "WHERE doctor_id = :doctorId AND DATE(appointment_time) = CURDATE() "
                             "AND status != '已取消'");
            countQuery.bindValue(":doctorId", doctor.id);
            
            if (countQuery.exec() && countQuery.next()) {
                doctor.bookedCount = countQuery.value("count").toInt();
            } else {
                doctor.bookedCount = 0;
            }
            
            // 计算剩余预约数
            doctor.remainingSlots = doctor.dailyPatientLimit - doctor.bookedCount;
            if (doctor.remainingSlots < 0) {
                doctor.remainingSlots = 0;
            }
            
            doctors.append(doctor);
        }
    }
    
    return doctors;
}

bool DatabaseManager::createPayment(const Payment& payment) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    QSqlQuery query(db);
    
    query.prepare("INSERT INTO payments (patient_id, payment_type, amount, status, "
                 "description, payment_time) VALUES (:patientId, :paymentType, "
                 ":amount, :status, :description, :paymentTime)");
    query.bindValue(":patientId", payment.patientId);
    query.bindValue(":paymentType", payment.paymentType);
    query.bindValue(":amount", payment.amount);
    query.bindValue(":status", payment.status);
    query.bindValue(":description", payment.description);
    query.bindValue(":paymentTime", payment.paymentTime);
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        return false;
    }
    
    return true;
}

QList<Payment> DatabaseManager::getPatientPayments(int patientId) {
    QList<Payment> payments;
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return QList<Payment>();
    QSqlQuery query(db);
    
    query.prepare("SELECT * FROM payments WHERE patient_id = :patientId "
                 "ORDER BY payment_time DESC");
    query.bindValue(":patientId", patientId);
    
    if (query.exec()) {
        while (query.next()) {
            Payment payment;
            payment.id = query.value("id").toInt();
            payment.patientId = query.value("patient_id").toInt();
            payment.paymentType = query.value("payment_type").toString();
            payment.amount = query.value("amount").toDouble();
            payment.status = query.value("status").toString();
            payment.description = query.value("description").toString();
            payment.paymentTime = query.value("payment_time").toDateTime();
            payments.append(payment);
        }
    }
    
    return payments;
}

// 医患沟通相关实现
QList<Doctor> DatabaseManager::getAllDoctors() {
    QList<Doctor> doctors;
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return QList<Doctor>();
    QSqlQuery query(db);
    
    // 查询所有医生信息，并计算当天的预约人数
    QString sql = "SELECT u.id, u.real_name, u.role, "
                  "d.department_id, d.title, d.specialty, d.education, "
                  "d.experience, d.introduction, d.work_time, d.consultation_fee, "
                  "d.daily_patient_limit, d.clinic_type, "
                  "dept.name as dept_name, "
                  "(SELECT COUNT(*) FROM appointments "
                  " WHERE doctor_id = u.id "
                  " AND DATE(appointment_time) = CURDATE() "
                  " AND status != '已取消') as booked_count "
                  "FROM users u "
                  "LEFT JOIN doctors d ON u.id = d.user_id "
                  "LEFT JOIN departments dept ON d.department_id = dept.id "
                  "WHERE u.role = 2 "
                  "ORDER BY dept.name, u.real_name";
    
    qDebug() << "DatabaseManager - 执行查询所有医生";
    qDebug() << "  SQL:" << sql;
    
    if (!query.exec(sql)) {
        qDebug() << "  查询失败:" << query.lastError().text();
        return doctors;
    }
    
    int count = 0;
    while (query.next()) {
        Doctor doctor;
        doctor.id = query.value("id").toInt();
        doctor.realName = query.value("real_name").toString();
        doctor.departmentId = query.value("department_id").toInt();
        doctor.title = query.value("title").toString();
        doctor.specialty = query.value("specialty").toString();
        doctor.education = query.value("education").toString();
        doctor.experience = query.value("experience").toInt();
        doctor.introduction = query.value("introduction").toString();
        doctor.workTime = query.value("work_time").toString();
        doctor.consultationFee = query.value("consultation_fee").toDouble();
        doctor.dailyPatientLimit = query.value("daily_patient_limit").toInt();
        doctor.clinicType = query.value("clinic_type").toString();
        doctor.departmentName = query.value("dept_name").toString();
        
        // 计算当天预约数和剩余预约数
        doctor.bookedCount = query.value("booked_count").toInt();
        doctor.remainingSlots = doctor.dailyPatientLimit - doctor.bookedCount;
        
        doctors.append(doctor);
        count++;
    }
    
    qDebug() << "  查询结果: 共" << count << "位医生";
    
    return doctors;
}

QList<Patient> DatabaseManager::getChatPatients(int doctorId) {
    QList<Patient> patients;
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return QList<Patient>();
    QSqlQuery query(db);
    
    // 查询有消息记录的患者（去重）
    query.prepare("SELECT DISTINCT u.id, u.real_name, p.gender, u.phone, "
                 "p.birth_date, p.address, "
                 "(SELECT MAX(send_time) FROM messages "
                 "  WHERE (sender_id = u.id AND receiver_id = :doctorId) "
                 "     OR (sender_id = :doctorId AND receiver_id = u.id)) as last_chat_time, "
                 "(SELECT COUNT(*) FROM messages "
                 "  WHERE sender_id = u.id AND receiver_id = :doctorId AND is_read = 0) as unread_count "
                 "FROM users u "
                 "LEFT JOIN patients p ON u.id = p.user_id "
                 "WHERE u.role = 1 "
                 "AND EXISTS (SELECT 1 FROM messages "
                 "            WHERE (sender_id = u.id AND receiver_id = :doctorId) "
                 "               OR (sender_id = :doctorId AND receiver_id = u.id)) "
                 "ORDER BY last_chat_time DESC");
    query.bindValue(":doctorId", doctorId);
    
    if (query.exec()) {
        while (query.next()) {
            Patient patient;
            patient.id = query.value("id").toInt();
            patient.realName = query.value("real_name").toString();
            patient.gender = query.value("gender").toString();
            patient.phone = query.value("phone").toString();
            patient.birthDate = query.value("birth_date").toDate();
            patient.address = query.value("address").toString();
            patients.append(patient);
        }
    } else {
        qDebug() << "获取聊天患者列表失败:" << query.lastError().text();
    }
    
    return patients;
}

bool DatabaseManager::sendMessage(const Message& message) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    QSqlQuery query(db);
    
    query.prepare("INSERT INTO messages (sender_id, receiver_id, message_content, send_time, is_read) "
                 "VALUES (:senderId, :receiverId, :content, :sendTime, :isRead)");
    query.bindValue(":senderId", message.senderId);
    query.bindValue(":receiverId", message.receiverId);
    query.bindValue(":content", message.messageContent);
    query.bindValue(":sendTime", message.sendTime);
    query.bindValue(":isRead", message.isRead);
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        qDebug() << "发送消息失败:" << m_lastError;
        return false;
    }
    
    int messageId = query.lastInsertId().toInt();
    
    // 获取发送者姓名和角色
    QSqlQuery senderQuery(db);
    senderQuery.prepare("SELECT real_name, role FROM users WHERE id = :senderId");
    senderQuery.bindValue(":senderId", message.senderId);
    QString senderName = "用户";
    int senderRole = 0;
    if (senderQuery.exec() && senderQuery.next()) {
        senderName = senderQuery.value("real_name").toString();
        senderRole = senderQuery.value("role").toInt();
    }
    
    // 获取接收者角色
    QSqlQuery receiverQuery(db);
    receiverQuery.prepare("SELECT role FROM users WHERE id = :receiverId");
    receiverQuery.bindValue(":receiverId", message.receiverId);
    int receiverRole = 0;
    if (receiverQuery.exec() && receiverQuery.next()) {
        receiverRole = receiverQuery.value("role").toInt();
    }
    
    // 创建通知给接收者
    QString messagePreview = message.messageContent;
    if (messagePreview.length() > 30) {
        messagePreview = messagePreview.left(30) + "...";
    }
    
    Notification notification;
    notification.userId = message.receiverId;
    notification.userRole = receiverRole;
    notification.type = "new_message";
    notification.title = "💬 您收到一条新消息";
    notification.content = QString("来自：%1\n内容：%2")
        .arg(senderName, messagePreview);
    notification.relatedId = messageId;
    notification.isRead = false;
    createNotification(notification);
    
    return true;
}

QList<Message> DatabaseManager::getChatHistory(int userId1, int userId2) {
    QList<Message> messages;
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return QList<Message>();
    QSqlQuery query(db);
    
    query.prepare("SELECT m.*, "
                 "u1.real_name as sender_name, "
                 "u2.real_name as receiver_name "
                 "FROM messages m "
                 "LEFT JOIN users u1 ON m.sender_id = u1.id "
                 "LEFT JOIN users u2 ON m.receiver_id = u2.id "
                 "WHERE (m.sender_id = :userId1 AND m.receiver_id = :userId2) "
                 "   OR (m.sender_id = :userId2 AND m.receiver_id = :userId1) "
                 "ORDER BY m.send_time ASC");
    query.bindValue(":userId1", userId1);
    query.bindValue(":userId2", userId2);
    
    if (query.exec()) {
        while (query.next()) {
            Message msg;
            msg.id = query.value("id").toInt();
            msg.senderId = query.value("sender_id").toInt();
            msg.receiverId = query.value("receiver_id").toInt();
            msg.messageContent = query.value("message_content").toString();
            msg.sendTime = query.value("send_time").toDateTime();
            msg.isRead = query.value("is_read").toBool();
            msg.senderName = query.value("sender_name").toString();
            msg.receiverName = query.value("receiver_name").toString();
            messages.append(msg);
        }
    }
    
    return messages;
}

int DatabaseManager::getUnreadCount(int receiverId, int senderId) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    QSqlQuery query(db);
    
    if (senderId > 0) {
        // 获取特定发送者的未读消息数
        query.prepare("SELECT COUNT(*) FROM messages "
                     "WHERE receiver_id = :receiverId AND sender_id = :senderId AND is_read = 0");
        query.bindValue(":receiverId", receiverId);
        query.bindValue(":senderId", senderId);
    } else {
        // 获取所有未读消息数
        query.prepare("SELECT COUNT(*) FROM messages "
                     "WHERE receiver_id = :receiverId AND is_read = 0");
        query.bindValue(":receiverId", receiverId);
    }
    
    if (query.exec() && query.next()) {
        return query.value(0).toInt();
    }
    
    return 0;
}

bool DatabaseManager::markAsRead(int receiverId, int senderId) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    QSqlQuery query(db);
    
    query.prepare("UPDATE messages SET is_read = 1 "
                 "WHERE receiver_id = :receiverId AND sender_id = :senderId AND is_read = 0");
    query.bindValue(":receiverId", receiverId);
    query.bindValue(":senderId", senderId);
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        return false;
    }
    
    return true;
}

// 考勤相关实现
bool DatabaseManager::checkIn(int doctorId) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    QSqlQuery query(db);
    
    QDate today = QDate::currentDate();
    QTime now = QTime::currentTime();
    
    query.prepare("INSERT INTO attendance_records (doctor_id, attendance_date, attendance_time) "
                 "VALUES (:doctorId, :date, :time)");
    query.bindValue(":doctorId", doctorId);
    query.bindValue(":date", today);
    query.bindValue(":time", now);
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        return false;
    }
    
    return true;
}

bool DatabaseManager::cancelCheckIn(int doctorId, const QDate& date) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    QSqlQuery query(db);
    
    query.prepare("DELETE FROM attendance_records WHERE doctor_id = :doctorId AND attendance_date = :date");
    query.bindValue(":doctorId", doctorId);
    query.bindValue(":date", date);
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        return false;
    }
    
    return query.numRowsAffected() > 0;
}

QList<AttendanceRecord> DatabaseManager::getAttendanceRecords(int doctorId, const QDate& startDate, const QDate& endDate) {
    QList<AttendanceRecord> records;
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return QList<AttendanceRecord>();
    QSqlQuery query(db);
    
    query.prepare("SELECT * FROM attendance_records "
                 "WHERE doctor_id = :doctorId AND attendance_date BETWEEN :startDate AND :endDate "
                 "ORDER BY attendance_date DESC");
    query.bindValue(":doctorId", doctorId);
    query.bindValue(":startDate", startDate);
    query.bindValue(":endDate", endDate);
    
    if (query.exec()) {
        while (query.next()) {
            AttendanceRecord record;
            record.id = query.value("id").toInt();
            record.doctorId = query.value("doctor_id").toInt();
            record.attendanceDate = query.value("attendance_date").toDate();
            record.attendanceTime = query.value("attendance_time").toTime();
            record.createTime = query.value("create_time").toDateTime();
            records.append(record);
        }
    }
    
    return records;
}

bool DatabaseManager::getTodayAttendance(int doctorId, AttendanceRecord& record) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    QSqlQuery query(db);
    
    QDate today = QDate::currentDate();
    query.prepare("SELECT * FROM attendance_records WHERE doctor_id = :doctorId AND attendance_date = :date");
    query.bindValue(":doctorId", doctorId);
    query.bindValue(":date", today);
    
    if (query.exec() && query.next()) {
        record.id = query.value("id").toInt();
        record.doctorId = query.value("doctor_id").toInt();
        record.attendanceDate = query.value("attendance_date").toDate();
        record.attendanceTime = query.value("attendance_time").toTime();
        record.createTime = query.value("create_time").toDateTime();
        return true;
    }
    
    return false;
}

bool DatabaseManager::createLeaveRequest(const LeaveRequest& request) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    QSqlQuery query(db);
    
    query.prepare("INSERT INTO leave_requests (doctor_id, phone, leave_type, start_date, end_date, reason, status) "
                 "VALUES (:doctorId, :phone, :leaveType, :startDate, :endDate, :reason, :status)");
    query.bindValue(":doctorId", request.doctorId);
    query.bindValue(":phone", request.phone);
    query.bindValue(":leaveType", request.leaveType);
    query.bindValue(":startDate", request.startDate);
    query.bindValue(":endDate", request.endDate);
    query.bindValue(":reason", request.reason);
    query.bindValue(":status", request.status.isEmpty() ? "已批准" : request.status);
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        return false;
    }
    
    return true;
}

bool DatabaseManager::cancelLeaveRequest(int requestId) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    QSqlQuery query(db);
    
    query.prepare("DELETE FROM leave_requests WHERE id = :requestId");
    query.bindValue(":requestId", requestId);
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        return false;
    }
    
    return query.numRowsAffected() > 0;
}

QList<LeaveRequest> DatabaseManager::getLeaveRequests(int doctorId) {
    QList<LeaveRequest> requests;
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return QList<LeaveRequest>();
    QSqlQuery query(db);
    
    query.prepare("SELECT * FROM leave_requests WHERE doctor_id = :doctorId ORDER BY request_time DESC");
    query.bindValue(":doctorId", doctorId);
    
    if (query.exec()) {
        while (query.next()) {
            LeaveRequest request;
            request.id = query.value("id").toInt();
            request.doctorId = query.value("doctor_id").toInt();
            request.phone = query.value("phone").toString();
            request.leaveType = query.value("leave_type").toString();
            request.startDate = query.value("start_date").toDate();
            request.endDate = query.value("end_date").toDate();
            request.reason = query.value("reason").toString();
            request.status = query.value("status").toString();
            request.requestTime = query.value("request_time").toDateTime();
            if (!query.value("cancel_time").isNull()) {
                request.cancelTime = query.value("cancel_time").toDateTime();
            }
            requests.append(request);
        }
    }
    
    return requests;
}

bool DatabaseManager::cancelLeave(int requestId) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    QSqlQuery query(db);
    
    query.prepare("UPDATE leave_requests SET status = '已销假', cancel_time = NOW() WHERE id = :requestId");
    query.bindValue(":requestId", requestId);
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        return false;
    }
    
    return query.numRowsAffected() > 0;
}

// 获取热门药物
QList<Medicine> DatabaseManager::getPopularMedicines() {
    QList<Medicine> medicines;
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return QList<Medicine>();
    QSqlQuery query(db);
    
    query.prepare("SELECT * FROM medicines WHERE is_popular = 1 ORDER BY name");
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        return medicines;
    }
    
    while (query.next()) {
        Medicine medicine;
        medicine.id = query.value("id").toInt();
        medicine.name = query.value("name").toString();
        medicine.category = query.value("category").toString();
        medicine.manufacturer = query.value("manufacturer").toString();
        medicine.specification = query.value("specification").toString();
        medicine.price = query.value("price").toDouble();
        medicine.usageInstruction = query.value("usage_instruction").toString();
        medicine.precautions = query.value("precautions").toString();
        medicine.isPopular = query.value("is_popular").toBool();
        medicine.createTime = query.value("create_time").toDateTime();
        medicines.append(medicine);
    }
    
    return medicines;
}

// 搜索药物
QList<Medicine> DatabaseManager::searchMedicines(const QString& keyword) {
    QList<Medicine> medicines;
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return QList<Medicine>();
    QSqlQuery query(db);
    
    query.prepare("SELECT * FROM medicines WHERE name LIKE :keyword OR category LIKE :keyword ORDER BY is_popular DESC, name");
    query.bindValue(":keyword", "%" + keyword + "%");
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        return medicines;
    }
    
    while (query.next()) {
        Medicine medicine;
        medicine.id = query.value("id").toInt();
        medicine.name = query.value("name").toString();
        medicine.category = query.value("category").toString();
        medicine.manufacturer = query.value("manufacturer").toString();
        medicine.specification = query.value("specification").toString();
        medicine.price = query.value("price").toDouble();
        medicine.usageInstruction = query.value("usage_instruction").toString();
        medicine.precautions = query.value("precautions").toString();
        medicine.isPopular = query.value("is_popular").toBool();
        medicine.createTime = query.value("create_time").toDateTime();
        medicines.append(medicine);
    }
    
    return medicines;
}

// 检查医生是否请假
bool DatabaseManager::isDoctorOnLeave(int doctorId, const QDate& date) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    QSqlQuery query(db);
    
    // 查询该日期是否在请假时间范围内，且状态为已批准
    query.prepare("SELECT COUNT(*) FROM leave_requests "
                  "WHERE doctor_id = :doctorId "
                  "AND :date BETWEEN start_date AND end_date "
                  "AND status = '已批准'");
    query.bindValue(":doctorId", doctorId);
    query.bindValue(":date", date.toString("yyyy-MM-dd"));
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        return false;
    }
    
    if (query.next()) {
        return query.value(0).toInt() > 0;
    }
    
    return false;
}

// 获取医生在时间段内的预约列表
QList<Appointment> DatabaseManager::getDoctorAppointmentsInRange(int doctorId, const QDate& startDate, const QDate& endDate) {
    QList<Appointment> appointments;
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return QList<Appointment>();
    QSqlQuery query(db);
    
    query.prepare("SELECT * FROM appointments "
                  "WHERE doctor_id = :doctorId "
                  "AND DATE(appointment_time) BETWEEN :startDate AND :endDate "
                  "AND status != '已取消' "
                  "ORDER BY appointment_time");
    query.bindValue(":doctorId", doctorId);
    query.bindValue(":startDate", startDate.toString("yyyy-MM-dd"));
    query.bindValue(":endDate", endDate.toString("yyyy-MM-dd"));
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        return appointments;
    }
    
    while (query.next()) {
        Appointment appointment;
        appointment.id = query.value("id").toInt();
        appointment.patientId = query.value("patient_id").toInt();
        appointment.doctorId = query.value("doctor_id").toInt();
        appointment.appointmentTime = query.value("appointment_time").toDateTime();
        appointment.status = query.value("status").toString();
        appointment.cancelReason = query.value("cancel_reason").toString();
        appointment.symptoms = query.value("symptoms").toString();
        appointment.medicalRecordId = query.value("medical_record_id").toInt();
        appointments.append(appointment);
    }
    
    return appointments;
}

// 通知相关实现
bool DatabaseManager::createNotification(const Notification& notification) {
    qDebug() << "=== 创建通知 ===";
    qDebug() << "用户ID:" << notification.userId << "角色:" << notification.userRole;
    qDebug() << "类型:" << notification.type;
    qDebug() << "标题:" << notification.title;
    qDebug() << "内容:" << notification.content;
    
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) {
        qDebug() << "❌ 数据库连接无效";
        return false;
    }
    QSqlQuery query(db);
    
    query.prepare("INSERT INTO notifications (user_id, user_role, type, title, content, related_id, is_read) "
                  "VALUES (:user_id, :user_role, :type, :title, :content, :related_id, :is_read)");
    query.bindValue(":user_id", notification.userId);
    query.bindValue(":user_role", notification.userRole);
    query.bindValue(":type", notification.type);
    query.bindValue(":title", notification.title);
    query.bindValue(":content", notification.content);
    query.bindValue(":related_id", notification.relatedId);
    query.bindValue(":is_read", notification.isRead);
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        qDebug() << "❌ 创建通知失败:" << m_lastError;
        return false;
    }
    
    qDebug() << "✅ 通知创建成功，ID:" << query.lastInsertId().toInt();
    return true;
}

QList<Notification> DatabaseManager::getUserNotifications(int userId, int userRole) {
    QList<Notification> notifications;
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return QList<Notification>();
    QSqlQuery query(db);
    
    query.prepare("SELECT * FROM notifications WHERE user_id = :user_id AND user_role = :user_role "
                  "ORDER BY create_time DESC LIMIT 100");
    query.bindValue(":user_id", userId);
    query.bindValue(":user_role", userRole);
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        return notifications;
    }
    
    while (query.next()) {
        Notification notification;
        notification.id = query.value("id").toInt();
        notification.userId = query.value("user_id").toInt();
        notification.userRole = query.value("user_role").toInt();
        notification.type = query.value("type").toString();
        notification.title = query.value("title").toString();
        notification.content = query.value("content").toString();
        notification.relatedId = query.value("related_id").toInt();
        notification.isRead = query.value("is_read").toBool();
        notification.createTime = query.value("create_time").toDateTime();
        notifications.append(notification);
    }
    
    return notifications;
}

bool DatabaseManager::markNotificationRead(int notificationId) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    QSqlQuery query(db);
    
    query.prepare("UPDATE notifications SET is_read = 1 WHERE id = :id");
    query.bindValue(":id", notificationId);
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        return false;
    }
    
    return true;
}

bool DatabaseManager::clearAllNotifications(int userId, int userRole) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return false;
    QSqlQuery query(db);
    
    query.prepare("DELETE FROM notifications WHERE user_id = :user_id AND user_role = :user_role");
    query.bindValue(":user_id", userId);
    query.bindValue(":user_role", userRole);
    
    if (!query.exec()) {
        m_lastError = query.lastError().text();
        return false;
    }
    
    return true;
}

int DatabaseManager::getUnreadNotificationCount(int userId, int userRole) {
    QSqlDatabase db = getThreadDatabase();
    if (!db.isValid()) return 0;
    QSqlQuery query(db);
    
    query.prepare("SELECT COUNT(*) as count FROM notifications WHERE user_id = :user_id AND user_role = :user_role AND is_read = 0");
    query.bindValue(":user_id", userId);
    query.bindValue(":user_role", userRole);
    
    if (!query.exec() || !query.next()) {
        m_lastError = query.lastError().text();
        return 0;
    }
    
    return query.value("count").toInt();
}

